
use rusqlite::{Connection, Result};
use std::collections::HashMap;
use std::fmt::{Debug, format};

#[derive(Debug, Clone)]
struct ColInfo {
    col_name: String,
    col_type: String,
    col_notnull: bool,
    //col_default : i32,
    col_pk: bool,
}

#[derive(Debug, Clone)]
struct TBInfo {
    tb_type: String,
    tb_name: String,
    tb_sql: String,
    tb_cols: Vec<ColInfo>,
}

#[derive(Debug)]
pub struct DBInfo {
    db_name: String,
    db_tables: Vec<TBInfo>,
    db_conn: Option<Connection>,
}

impl DBInfo {
    pub fn read_db(path: &str) -> Result<DBInfo> {
        let mut db_info = DBInfo {
            db_name: "".to_string(),
            db_tables: Vec::new(),
            db_conn: None,
        };
        let conn =
            Connection::open(path).expect(String::from("open sqlite path failed! ").as_str());
        {
            //查询DB中所有表的信息
            let mut stmt = conn
                .prepare("select * from sqlite_master")
                .expect("prepare table query stmt failed!");
            let mut rows = stmt.query([]).expect("query table failed !");
            while let Some(row) = rows.next()? {
                //判断是否时table类型
                let table_type: String = row.get(0)?;
                if table_type != "table" {
                    continue;
                }

                db_info.db_tables.push(TBInfo {
                    tb_type: row.get(0)?,
                    tb_name: row.get(1)?,
                    tb_sql: row.get(4)?,
                    tb_cols: Vec::new(),
                })
            }
        }

        //查询每张表的字段信息
        for table in db_info.db_tables.iter_mut() {
            let sql = format!("pragma table_info({})", table.tb_name);
            let mut stmt = conn
                .prepare(sql.as_str())
                .expect("prepare col stmt failed!");
            let mut rows = stmt.query([]).expect("query bol failed!");
            while let Some(row) = rows.next()? {
                table.tb_cols.push(ColInfo {
                    col_name: row.get(1)?,
                    col_type: row.get(2)?,
                    col_notnull: row.get(3)?,
                    //col_default: row.get(4)?,
                    col_pk: row.get(5)?,
                })
            }
        }

        db_info.db_conn = Some(conn);
        Ok(db_info)
    }
}

//pub fn check_add_table_col(old_sqlite_path : &str, new_sqlite_path : &str) ->Result<()>{
pub fn check_add_table_col<'a>(old_db: &'a mut DBInfo, new_db: &'a DBInfo) -> Result<()> {
    //构建一个临时Hashmap用于查询表信息
    let mut old_table_map = HashMap::new();
    for old_table in &mut old_db.db_tables {
        old_table_map.insert(String::from(old_table.tb_name.as_str()), old_table.clone());
    }

    //遍历new tables，与old tables 做对比
    new_db.db_tables.iter().for_each(|new_db_table| {
        match old_table_map.get(&new_db_table.tb_name) {
            None => {
                //old DB 中没有该table 则新建
                match old_db
                    .db_conn
                    .as_mut()
                    .unwrap()
                    .execute(new_db_table.tb_sql.as_str(), [])
                {
                    Ok(_) => {}
                    Err(_) => {
                        println!("error execute sql: {:?}", new_db_table.tb_sql);
                    }
                }
            }
            Some(old_db_table) => {
                //old table 中有该 table
                //遍历new table中的每一列，并为old table 补全缺失列。
                for new_col in &new_db_table.tb_cols {
                    let col_need_handle = old_db_table.tb_cols.iter().find(|old_col| -> bool {
                        //筛选出同名列存入col_need_handle
                        old_col.col_name == new_col.col_name
                    });

                    match col_need_handle {
                        None => {
                            //没有同名列则创建
                            //let stmt = old_db.db_conn.as_mut().unwrap().prepare("ALTER TABLE ?1 ADD COLUMN ?2 ?3 ?4");
                            let cluster = |b: bool| -> &str {
                                match b {
                                    true => {
                                        match new_col.col_type.as_str() {
                                            "INTEGER" => "NOT NULL DEFAULT 0",
                                            "TEXT" => "NOT NULL DEFAULT ''",
                                            "BOOL" => "NOT NULL DEFAULT true",
                                            &_ => {
                                                //println!("[sqlite_update] warning {} type not catch!",new_col.col_type);
                                                ""
                                            }
                                        };
                                        ""
                                    }
                                    false => "",
                                }
                            };
                            let sql = format!(
                                "ALTER TABLE {} ADD COLUMN {} {} {};",
                                old_db_table.tb_name.as_str(),
                                new_col.col_name.as_str(),
                                new_col.col_type.as_str(),
                                cluster(new_col.col_notnull)
                            );
                            old_db
                                .db_conn
                                .as_mut()
                                .unwrap()
                                .execute(sql.as_str(), ())
                                .expect("TODO: panic message");
                            println!(
                                "ALTER TABLE ADD {} {}",
                                old_db_table.tb_name, new_col.col_name
                            );
                        }
                        Some(col) => {
                            //同名列暂不处理
                        }
                    }
                }
            }
        }
    });

    println!("{:?}", new_db);

    Ok(())
}

pub fn check_move_table_col(
    mut db: DBInfo,
    table_name: &str,
    from_col_name: &str,
    to_col_name: &str,
) -> Result<(), String> {
    match db
        .db_tables
        .iter_mut()
        .find(|tb_info| -> bool { tb_info.tb_name == table_name })
    {
        None => {
            return Err("Invalid table name!".to_string());
        }
        Some(tb_info) => {
            let from_col_info = tb_info
                .tb_cols
                .iter()
                .find(|from_col| -> bool { from_col.col_name == from_col_name });

            let to_col_info = tb_info
                .tb_cols
                .iter()
                .find(|to_col| to_col.col_name == to_col_name);

            if from_col_info.is_none() {
                return Err("from col name can not find!".to_string());
            }

            if to_col_info.is_none() {
                return Err("to col can name not find!".to_string());
            }

            if from_col_info.unwrap().col_type != to_col_info.unwrap().col_type {
                return Err("from col and to col are not matched!".to_string());
            }

            //from列 向 to列 数据拷贝
            let sql = format!(
                "UPDATE {} SET {} = {};",
                table_name, to_col_name, from_col_name
            );
            db.db_conn.as_ref()
                .unwrap()
                .execute(sql.as_str(), [])
                .expect("run copy sql failed!");

            //from列 数据删除
            let mut value = String::new();
            match from_col_info.unwrap().col_type.to_uppercase().as_str() {
                "INTEGER" => {
                    value = "0".parse().unwrap();
                },
                "TEXT" => {
                    value = "''".parse().unwrap();
                }
                "BLOB" => {
                    value = "0".parse().unwrap();
                }
                "BOOL" => {
                    value = "false".parse().unwrap();
                }
                &_ => {
                    return Err("no such col type!".to_string());
                }
            }
            let sql = format!(
                "UPDATE {} SET {} = {};",
                table_name, from_col_name,value
            );
            db.db_conn.as_ref()
                .unwrap()
                .execute(sql.as_str(), [])
                .expect("run copy sql failed!");

        }
    }
    Ok(())
}
